using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Common;
using RMAS.DAL;

namespace RMAS.DAL
{
    /// <summary>
    /// Summary description for DM_PARTNERINFO.
     /// </summary>
	public class DM_PARTNERINFODao
    {
	    private string _SQLSelectByKey;
	    private string _SQLUpdate;
	    private string _SQLInsert;
	    private string _SQLDelete;
	    private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion
        
        #region Constructor

        public DM_PARTNERINFODao()
        {
            _dbConnection = null;
		    InitSqlQuery();
        }

        public DM_PARTNERINFODao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
       {
            string query;

            query = @"SELECT PartnerID, TypePartner, Name, Address, Phone, Fax, Email, First, PromoteTime, PromoteAll, IsVIP, IDPOS FROM DM_PARTNERINFO WHERE (PartnerID = @PartnerID)";
	        _SQLSelectByKey = query;

            query = @"UPDATE DM_PARTNERINFO SET TypePartner = @TypePartner, Name = @Name, Address = @Address, Phone = @Phone, Fax = @Fax, Email = @Email, First = @First, PromoteTime = @PromoteTime, PromoteAll = @PromoteAll, IsVIP = @IsVIP, IDPOS = @IDPOS WHERE (PartnerID = @PartnerID)";
	        _SQLUpdate = query;

            query = @"INSERT INTO DM_PARTNERINFO VALUES (@TypePartner, @Name, @Address, @Phone, @Fax, @Email, @First, @PromoteTime, @PromoteAll, @IsVIP, @IDPOS)";
	        _SQLInsert = query;

            query = @"DELETE FROM DM_PARTNERINFO WHERE (PartnerID = @PartnerID)";
	        _SQLDelete = query;

            query = @"SELECT PartnerID, TypePartner, Name, Address, Phone, Fax, Email, First, PromoteTime, PromoteAll, IsVIP, IDPOS FROM DM_PARTNERINFO";
	        _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

				if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(DM_PARTNERINFO obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                         DalTools.addDbDataParameter(command, "PartnerID", obj.PartnerID, DbType.Int32);
                         DalTools.addDbDataParameter(command, "TypePartner", obj.TypePartner, DbType.Int32);
         DalTools.addDbDataParameter(command, "Name", obj.Name, DbType.String);
         DalTools.addDbDataParameter(command, "Address", obj.Address, DbType.String);
         DalTools.addDbDataParameter(command, "Phone", obj.Phone, DbType.String);
         DalTools.addDbDataParameter(command, "Fax", obj.Fax, DbType.String);
         DalTools.addDbDataParameter(command, "Email", obj.Email, DbType.String);
         DalTools.addDbDataParameter(command, "First", obj.First, DbType.Double);
         DalTools.addDbDataParameter(command, "PromoteTime", obj.PromoteTime, DbType.Double);
         DalTools.addDbDataParameter(command, "PromoteAll", obj.PromoteAll, DbType.Double);
         DalTools.addDbDataParameter(command, "IsVIP", obj.IsVIP, DbType.Boolean);
         DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(DM_PARTNERINFO obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                         DalTools.addDbDataParameter(command, "PartnerID", obj.PartnerID, DbType.Int32);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(DM_PARTNERINFO obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(DM_PARTNERINFO obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties
     
        private DM_PARTNERINFOList SelectListByCommand(IDbCommand command)
        {
            DM_PARTNERINFOList list = new DM_PARTNERINFOList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
		DM_PARTNERINFO obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new DM_PARTNERINFO();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
		
        private DM_PARTNERINFOList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            DM_PARTNERINFOList list = new DM_PARTNERINFOList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
				DM_PARTNERINFO obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
				command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new DM_PARTNERINFO();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
	
        private void ReadProperties(DM_PARTNERINFO obj, IDataReader reader)
        {
            try
            {
                                if(!(reader["PartnerID"] is DBNull))
		{
			obj.PartnerID = (Int32)reader["PartnerID"];
		}
                if(!(reader["TypePartner"] is DBNull))
		{
			obj.TypePartner = (Int32)reader["TypePartner"];
		}
                if(!(reader["Name"] is DBNull))
		{
			obj.Name = (string)reader["Name"];
		}
                if(!(reader["Address"] is DBNull))
		{
			obj.Address = (string)reader["Address"];
		}
                if(!(reader["Phone"] is DBNull))
		{
			obj.Phone = (string)reader["Phone"];
		}
                if(!(reader["Fax"] is DBNull))
		{
			obj.Fax = (string)reader["Fax"];
		}
                if(!(reader["Email"] is DBNull))
		{
			obj.Email = (string)reader["Email"];
		}
                if(!(reader["First"] is DBNull))
		{
			obj.First = (double)reader["First"];
		}
                if(!(reader["PromoteTime"] is DBNull))
		{
			obj.PromoteTime = (double)reader["PromoteTime"];
		}
                if(!(reader["PromoteAll"] is DBNull))
		{
			obj.PromoteAll = (double)reader["PromoteAll"];
		}
                if(!(reader["IsVIP"] is DBNull))
		{
			obj.IsVIP = (bool)reader["IsVIP"];
		}
                if(!(reader["IDPOS"] is DBNull))
		{
			obj.IDPOS = (string)reader["IDPOS"];
		}
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion
      
        public DM_PARTNERINFO SelectByKey(Int32 PartnerID)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "PartnerID", PartnerID, DbType.Int32);
      	    DM_PARTNERINFOList list =  SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_PARTNERINFO SelectByKey(Int32 PartnerID, IDbTransaction trans)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "PartnerID", PartnerID, DbType.Int32);
      	    DM_PARTNERINFOList list =  SelectListByCommand(command, trans);
			
            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public DM_PARTNERINFOList SelectAll(int TypePartner)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            _SQLSelectAll = _SQLSelectAll + " WHERE TypePartner = @TypePartner";
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " AND IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            DalTools.addDbDataParameter(command, "TypePartner", TypePartner, DbType.Int16);
            command.CommandText = _SQLSelectAll;
      	    DM_PARTNERINFOList list =  SelectListByCommand(command);
	        return list;
        }
        public DM_PARTNERINFOList SelectAll(IDbTransaction trans, int TypePartner)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            _SQLSelectAll = _SQLSelectAll + " WHERE TypePartner = @TypePartner";
            if (!StaticVar.IsServer)
            {
                _SQLSelectAll = _SQLSelectAll + " AND IDPOS = @IDPOS";
                DalTools.addDbDataParameter(command, "IDPOS", StaticVar.CPUID, DbType.String);
            }
            DalTools.addDbDataParameter(command, "TypePartner", TypePartner, DbType.Int16);
            command.CommandText = _SQLSelectAll;
      	    DM_PARTNERINFOList list =  SelectListByCommand(command, trans);
	        return list;
        }
         public DM_PARTNERINFOList SelectByCommand(string Command)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = Command;
      	    DM_PARTNERINFOList list =  SelectListByCommand(command);
	        return list;
        }

        public DM_PARTNERINFOList SelectByCondition(DM_PARTNERINFO obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_PARTNERINFO " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_PARTNERINFOList list = SelectListByCommand(command, trans);
            return list;
        }
		
		 public DM_PARTNERINFOList SelectByCondition(DM_PARTNERINFO obj)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM DM_PARTNERINFO " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DM_PARTNERINFOList list = SelectListByCommand(command);
            return list;
        }
        private void DeleteByCondition(DM_PARTNERINFO obj, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM DM_PARTNERINFO " + GetCondtionQuery(obj) + ";";
            FillParamToCommand(command, obj);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(DM_PARTNERINFO obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            	if (obj.IsSetPartnerID)
	{
    	    s += " AND PartnerID = @PartnerID ";
	}
	if (obj.IsSetTypePartner)
	{
    	    s += " AND TypePartner = @TypePartner ";
	}
	if (obj.IsSetName)
	{
    	    s += " AND Name = @Name ";
	}
	if (obj.IsSetAddress)
	{
    	    s += " AND Address = @Address ";
	}
	if (obj.IsSetPhone)
	{
    	    s += " AND Phone = @Phone ";
	}
	if (obj.IsSetFax)
	{
    	    s += " AND Fax = @Fax ";
	}
	if (obj.IsSetEmail)
	{
    	    s += " AND Email = @Email ";
	}
	if (obj.IsSetFirst)
	{
    	    s += " AND First = @First ";
	}
	if (obj.IsSetPromoteTime)
	{
    	    s += " AND PromoteTime = @PromoteTime ";
	}
	if (obj.IsSetPromoteAll)
	{
    	    s += " AND PromoteAll = @PromoteAll ";
	}
	if (obj.IsSetIsVIP)
	{
    	    s += " AND IsVIP = @IsVIP ";
	}
	if (obj.IsSetIDPOS)
	{
    	    s += " AND IDPOS = @IDPOS ";
	}
            if (s.Length > 0)
            {
                s =" WHERE "+s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, DM_PARTNERINFO obj)
        {
	    if (obj == null) { return;}
            	            if (obj.IsSetPartnerID)
            {
            	DalTools.addDbDataParameter(command, "PartnerID", obj.PartnerID, DbType.Int32); 
            }
            if (obj.IsSetTypePartner)
            {
            	DalTools.addDbDataParameter(command, "TypePartner", obj.TypePartner, DbType.Int32); 
            }
            if (obj.IsSetName)
            {
            	DalTools.addDbDataParameter(command, "Name", obj.Name, DbType.String); 
            }
            if (obj.IsSetAddress)
            {
            	DalTools.addDbDataParameter(command, "Address", obj.Address, DbType.String); 
            }
            if (obj.IsSetPhone)
            {
            	DalTools.addDbDataParameter(command, "Phone", obj.Phone, DbType.String); 
            }
            if (obj.IsSetFax)
            {
            	DalTools.addDbDataParameter(command, "Fax", obj.Fax, DbType.String); 
            }
            if (obj.IsSetEmail)
            {
            	DalTools.addDbDataParameter(command, "Email", obj.Email, DbType.String); 
            }
            if (obj.IsSetFirst)
            {
            	DalTools.addDbDataParameter(command, "First", obj.First, DbType.Double); 
            }
            if (obj.IsSetPromoteTime)
            {
            	DalTools.addDbDataParameter(command, "PromoteTime", obj.PromoteTime, DbType.Double); 
            }
            if (obj.IsSetPromoteAll)
            {
            	DalTools.addDbDataParameter(command, "PromoteAll", obj.PromoteAll, DbType.Double); 
            }
            if (obj.IsSetIsVIP)
            {
            	DalTools.addDbDataParameter(command, "IsVIP", obj.IsVIP, DbType.Boolean); 
            }
            if (obj.IsSetIDPOS)
            {
            	DalTools.addDbDataParameter(command, "IDPOS", obj.IDPOS, DbType.String); 
            }
        }
        #endregion
    }
}